package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MOrg;
import org.compiere.model.MProduct;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.utils.AnalogConvertor_RU;
import org.utils.fwMoney;

import extend.org.compiere.utils.Util;

public class MM_Form_325 extends SvrProcess {
	//Form_325_
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	private Timestamp p_DateFrom = null;
	private Timestamp p_DateTo = null;
	/** */
	String file;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("Date_From"))
				p_DateFrom = (Timestamp)para[i].getParameter();
			else if (name.equals("Date_To"))
				p_DateTo = (Timestamp)para[i].getParameter();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

	}

	@Override
	protected String doIt() throws Exception {
		file = Util.localFilePath + "Form_325_.xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		HSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		//Style for cells
		CellStyle freeStyle = wb.createCellStyle();
		freeStyle.setBorderBottom((short)1);
		freeStyle.setBorderTop((short)1);
		freeStyle.setBorderLeft((short)1);
		freeStyle.setBorderRight((short)1);
		freeStyle.setFont(sheet.getRow(7).getCell(0).getCellStyle().getFont(wb));
		freeStyle.setWrapText(true);
		freeStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		freeStyle.setAlignment(CellStyle.ALIGN_CENTER);
		
		sheet.getRow(7).getCell(0).setCellValue(MOrg.get(m_ctx,Env.getAD_Org_ID(m_ctx)).getName());
		
		sheet.getRow(12).getCell(33).setCellValue(new SimpleDateFormat("dd").format(p_DateFrom == null ? new Timestamp(System.currentTimeMillis()): p_DateFrom));
		sheet.getRow(12).getCell(36).setCellValue(new SimpleDateFormat("MMMMM").format(p_DateFrom == null ? new Timestamp(System.currentTimeMillis()): p_DateFrom));
		
		R = sheet.getRow(13);
		R.getCell(33).setCellValue(new SimpleDateFormat("dd").format(p_DateTo == null ? new Timestamp(System.currentTimeMillis()): p_DateTo));
		R.getCell(36).setCellValue(new SimpleDateFormat("MMMMM").format(p_DateTo == null ? new Timestamp(System.currentTimeMillis()): p_DateTo));
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT DateAcct,M_Product_ID,Qty FROM MM_Crockery WHERE DateAcct BETWEEN "+ DB.TO_DATE(p_DateFrom,false) +" AND "+DB.TO_DATE(p_DateTo,false);
		try{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			Timestamp p_DateAcct;
			int iM_Product_ID = 0;
			int iQty = 0;
			int row = 16;
			while(rs.next()){
				row++;
				p_DateAcct = rs.getTimestamp(1);
				iM_Product_ID = rs.getInt(2);
				MProduct product = new MProduct(getCtx(), iM_Product_ID, get_TrxName());
				iQty = rs.getInt(3);
				
				sheet.shiftRows(row, row+7, 1);
				
				R = sheet.getRow(row);
				for(int i=0; i<=33;i++)
				{
					C = R.createCell(i);
					C.setCellStyle(freeStyle);
				}
				//col1
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 4));
				R.getCell(0).setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(p_DateAcct));
				//col2
				sheet.addMergedRegion(new CellRangeAddress(row, row, 5, 14));
				R.getCell(5).setCellValue(product.getName());
				//col3
				sheet.addMergedRegion(new CellRangeAddress(row, row, 15, 24));
				R.getCell(15).setCellValue(new AnalogConvertor_RU().toString(iQty)+"("+String.valueOf(iQty)+")");
				//col4
				sheet.addMergedRegion(new CellRangeAddress(row, row, 25, 27));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 28, 30));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 31, 33));
			}
			
		}
		catch(Exception e){
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(file);
		wb.write(fileOut);
		fileOut.close();
		// Open the file with the application associated
					Util.launchFile(new File(file));
				//
		
		return Msg.translate(m_ctx, "Success");
	}

}
